package cn.tit.ias.util;

import java.util.List;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

/**
 * 
 * 
 * @Description:处理excel文件
 * @author: 隔山海
 * @date: 2019年6月5日 下午9:14:07
 *
 */
public class HandleExcelUtil {
	private int totalRows = 0;// 总行数
	private int totalCells = 0;// 总列数

	public Map<String, List<List<String>>> read(String fileName)
			throws EncryptedDocumentException, org.apache.poi.openxml4j.exceptions.InvalidFormatException {
		Map<String, List<List<String>>> maps = new HashMap<String, List<List<String>>>();
		if (fileName == null || !fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")) {
            System.out.println("格式不正确或未指定文件");
			return maps;   

		}
		System.out.println(fileName);
		File file = new File(fileName);
		if (file == null || !file.exists())
			return maps;
		try {
			//创建并获取文件流
			Workbook wb = WorkbookFactory.create(new FileInputStream(file));
			//读取文件
			maps = read(wb);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return maps;
	}

	public int getTotalRows() {
		return totalRows;
	}

	public int getTotalCells() {
		return totalCells;
	}

	/**
	 * 
	 * @Title: read   
	 * @Description: 读取文件流到map中   
	 * @param: @param wb 文件对象
	 * @param: @return   存储文件中数据的map   
	 * @return: Map<String,List<List<String>>>      
	 * @throws
	 */
	private Map<String, List<List<String>>> read(Workbook wb) {
		Map<String, List<List<String>>> maps = new HashMap<String, List<List<String>>>();
		int number = wb.getNumberOfSheets();
		if (number > 0) {
			for (int i = 0; i < number; i++) { // 循环每个工作表
				List<List<String>> list = new ArrayList<List<String>>();
				int delnumber = 0;// 第一页去除行数
				Sheet sheet = wb.getSheetAt(i);
				System.out.println(sheet.getSheetName());
				this.totalRows = sheet.getPhysicalNumberOfRows() - delnumber; // 获取工作表中行数
				if (this.totalRows >= 1 && sheet.getRow(delnumber) != null) {
					this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells(); // 得到当前行的所有单元格
					for (int j = 0; j < totalRows; j++) {
						List<String> rowLst = new ArrayList<String>();
						for (int f = 0; f < totalCells; f++) {
							if (totalCells > 0) {
								String value = getCell(sheet.getRow(j).getCell(f));
								rowLst.add(value);
							}
						}
						list.add(rowLst);
					}
				}
				maps.put(sheet.getSheetName(), list);
			}
		}
		return maps;
	}

	/**
	 * 
	 * @Title: getCell @Description: 获取一行数据 @param: @param
	 * cell @param: @return @return: String @throws
	 */
	public String getCell(Cell cell) {
		String cellValue = null;
		HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
		cellValue = hSSFDataFormatter.formatCellValue(cell); // 使用EXCEL原来格式的方式取得值
		return cellValue;
	}

	/**
	 * 处理资产编号，组成新的资产信息
	 * 
	 * @param map
	 * @return
	 */
	public static List<List<String>> handleExcel(Map<String, List<List<String>>> map) {
		// 是否有字母
		boolean isLetter = false;
		// 存放处理后所有的list1集合
		List<List<String>> list = new ArrayList<>();
		for (int m = 0; m < map.size(); m++) {
			// 循环每一张表
			for (int i = 1; i < map.get("Sheet" + (m + 1)).size(); i++) {
				// 表中的一行数据
				List<String> excelRowDate = map.get("Sheet" + (m + 1)).get(i);
				// 获取每一行数据中资产编号的范围
				String assetRange = excelRowDate.get(3);
				// 判断资产编号中是否有字母
				for (int k = 0; k < assetRange.length(); k++) {
					if (Character.isLetter(assetRange.charAt(k))) {
						isLetter = true;
					}
				}
				if (isLetter) {
					list.addAll(hasLetter(assetRange, excelRowDate));
				} else {
					list.addAll(noLetter(assetRange, excelRowDate));
				}

			}
		}

		return list;

	}

	/**
	 * 处理有字符的资产编号
	 * 
	 * @param assetRange
	 * @param excelRowDate
	 * @return
	 */
	public static List<List<String>> hasLetter(String assetRange, List<String> excelRowDate) {
		// 提取资产编号范围中的字母
		String assetNumchar = assetRange.replaceAll("[^(a-zA-Z)]", "");
		// 提取资产编号范围中的数字
		String number = assetRange.replaceAll("[^(0-9)]", "");
		// 末尾编号的数字字符
		String lastString = number.substring(7, 14);
		// 起始编号的数字字符
		String firString = number.substring(0, 7);
		int first = Integer.parseInt(firString);
		int last = Integer.parseInt(lastString);
		// 这一种型号的资产有多少个
		int nu = last - first;
		String newNum = firString;
		List<List<String>> listoneBranch = new ArrayList<>();
		for (int j = 0; j <= nu; j++) {
			// 用来临时存放每一个资产处理后的的主要信息
			List<String> list1 = new ArrayList<>();
			// 处理后的资产编号
			newNum = String.valueOf(first) + assetNumchar.substring(1);

			list1.add(excelRowDate.get(1));
			list1.add(excelRowDate.get(2));
			list1.add(newNum);
			list1.add(excelRowDate.get(4));
			listoneBranch.add(list1);
			first++;
		}
		return listoneBranch;

	}

	/**
	 * 没有字母的资产编号
	 * 
	 * @param assetRange
	 * @param excelRowDate
	 * @return
	 */
	public static List<List<String>> noLetter(String assetRange, List<String> excelRowDate) {
		int first = Integer.parseInt(assetRange.substring(0, 8));
		int last = Integer.parseInt(assetRange.substring(9, 17));
		// 这一种型号的资产有多少个
		int nu = last - first;
		String newNum = assetRange.substring(0, 8);
		List<List<String>> listoneBranch = new ArrayList<>();
		for (int j = 0; j <= nu; j++) {
			// 用来临时存放每一个资产处理后的的主要信息
			List<String> list1 = new ArrayList<>();
			// 处理后的资产编号
			newNum = String.valueOf(first);

			list1.add(excelRowDate.get(1));
			list1.add(excelRowDate.get(2));
			list1.add(newNum);
			list1.add(excelRowDate.get(4));
			listoneBranch.add(list1);
			first++;
		}
		return listoneBranch;

	}

}
